import * as vscode from 'vscode';
import * as CombineCellRangeAllowBlank from './CombineCellRangeAllowBlank';
import * as ExcelUtils from './ExcelUtils';

export function doGenSQL(dims: { dimName: string, dimCode: string }[], mDims: { dimName: string, dimCode: string }[], tableName: string) {
  let dims_list: string[] = [];
  dims.forEach(dim => {
    // fieldName(systemField)
    dims_list.push(dim.dimCode);
  });
  let select_dims_str = dims_list.join(', ');
  let indent_select_dims = dims_list.map(dim => `  ${dim},`);
  let m_Dim_str = mDims[0].dimCode;


  let whereClause = "";
  let orderClause = "";
  if (tableName === "SINOPEC.ZBYY.CWYY.004_QMYS.H/F7AS_ZB_H_CW0003") {
    orderClause = genOrderClause(dims_list, ['GHFISCYEAR']);
  } else if (tableName === "SINOPEC.ZBYY.CWYY.004_QMYS.H/F7AS_ZB_H_CW0004") {
    orderClause = genOrderClause(dims_list, ['GHBBZDDATE']);
  } else {
    orderClause = genOrderClause(dims_list, ['BBZD_DATE']);
  }

  return [
    'SELECT',
    ...indent_select_dims,
    `  SUM(${m_Dim_str}) AS result`,
    `FROM "_SYS_BIC"."${tableName}"`,
    `WHERE ${whereClause}`,
    `GROUP BY ${select_dims_str}`,
    `ORDER BY ${orderClause}`
  ].join('\n');
}



function genOrderClause(dims_list: string[], desc_dims: string[]) {
  let asc_list: string[] = [];
  let desc_list: string[] = [];
  dims_list.forEach(dim => {
    if (desc_dims.includes(dim)) {
      desc_list.push(dim);
    } else {
      asc_list.push(dim);
    }
  });
  let orderClause = [];
  if (asc_list.length > 0) {
    orderClause.push(asc_list.join(', ') + ' ASC');
  }
  if (desc_list.length > 0) {
    orderClause.push(desc_list.join(', ') + ' DESC');
  }
  return orderClause.join(', ');
}


export async function descriptionToSqlCondition(tColumn: string, cColumn: string, rColumn: string) {
	const editor = vscode.window.activeTextEditor;
	if (!editor) {
		vscode.window.showErrorMessage('No active editor found!');
		return;
	}

	const selection = editor.selection;
	const selectedText = editor.document.getText(selection);

	if (!selectedText) {
		vscode.window.showErrorMessage('No text selected!');
		return;
	}

	const regex = /([a-zA-Z0-9]+)表([^a-zA-Z]*([a-zA-Z]+)([0-9]+))+/;
	const match = selectedText.match(regex);
	if (!match) {
		vscode.window.showErrorMessage('Need select XX表XX单元格!');
		return;
	}
	const form = match[1];
	const partRegex = /([a-zA-Z0-9]+)表(.+)/;
	const partMatch = selectedText.match(partRegex);
	if (!partMatch) {
		vscode.window.showErrorMessage('Need select XX表XX单元格!');
		return;
	}
	const cellsPart = partMatch[2];
	const cellRegex = /([a-zA-Z]+)([0-9]+)/g;
	const cellMatch = cellsPart.match(cellRegex);
	if (!cellMatch) {
		vscode.window.showErrorMessage('Need select XX表XX单元格!');
		return;
	}
	const references: CombineCellRangeAllowBlank.CellArea[] = CombineCellRangeAllowBlank.groupExcelCells(cellMatch);
	const whereClauses: string[] = [];
	for (const reference of references) {
		let colExpr = "";
		if (reference.columns.length === 1) {
			colExpr = `= '${ExcelUtils.excelColumnToNumber(reference.columns[0])}'`;
		} else {
			const columnsJoin = reference.columns.map(c=>ExcelUtils.excelColumnToNumber(c)).map(c=>`'${c}'`).join(",");
			colExpr = `IN (${columnsJoin})`;
		}
		let rowExpr = "";
		if (reference.rows.length === 1) {
			rowExpr = `= '${reference.rows[0]}'`;
		} else {
			const rowsJoin = reference.rows.map(r=>`'${r}'`).join(",");
			rowExpr = `IN (${rowsJoin})`;
		}
		const whereClause = `${tColumn} = '${form}' AND ${cColumn} ${colExpr} AND ${rColumn} ${rowExpr}`;
		whereClauses.push(whereClause);
	}

	try {
		const text = match[0];
		const finalClause = whereClauses.length === 1
			? whereClauses[0]
			: whereClauses.map(cl => {return `(${cl})`;}).join(" OR ");
		await vscode.env.clipboard.writeText(finalClause);
		vscode.window.showInformationMessage(`${text} copied to clipboard!`);
	} catch (error) {
		vscode.window.showErrorMessage(`Failed to copy text: ${error}`);
	}
}



